/**
 * All rights Reserved, Designed By 溪云阁
 * Copyright:    Copyright(C) 2016-2020
 */

package com.module.boots.coder.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang.StringUtils;

import com.module.boots.coder.data.ColumnData;

/**
 * mysql创建bean工具类
 * @author：溪云阁
 * @date：2020年9月3日
 */

public class MysqlBeanUtils {

    private static String url;

    private static String userName;

    private static String password;

    private static String dbName;

    /**
     * 初始化mysql信息
     * @author 溪云阁
     * @param ip
     * @param port
     * @param dbName
     * @param userName
     * @param password void
     */
    public static void setMysqlInfo(String ip, String port, String dbName, String userName, String password) {
        MysqlBeanUtils.url = "jdbc:mysql://" + ip + ":" + port + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8";
        MysqlBeanUtils.userName = userName;
        MysqlBeanUtils.password = password;
        MysqlBeanUtils.dbName = dbName;
    }

    /**
     * 获取mysql连接
     * @author 溪云阁
     * @return
     * @throws SQLException Connection
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection(MysqlBeanUtils.url, MysqlBeanUtils.userName, MysqlBeanUtils.password);
    }

    /**
     * 获取所有的列数据
     * @author 溪云阁
     * @param tableName
     * @return
     * @throws SQLException List<ColumnData>
     */
    private static List<ColumnData> getColumnDatas(String tableName) throws Exception {
        final String SQLColumns = "select column_name ,data_type,column_comment,0,0,character_maximum_length,is_nullable nullable,column_key from information_schema.columns where table_name =  '"
                + tableName + "' " + "and table_schema =  '"
                + dbName + "'";
        final Connection con = getConnection();
        final PreparedStatement ps = con.prepareStatement(SQLColumns);
        final List<ColumnData> columnList = new ArrayList<>();
        final ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            final String name = rs.getString(1);
            final String comment = rs.getString(3);
            final String precision = rs.getString(4);
            final String scale = rs.getString(5);
            final String charmaxLength = rs.getString(6) == null ? "" : rs.getString(6);
            final String nullable = ColumnUtils.getNullAble(rs.getString(7));
            final String columnKey = rs.getString("column_key");
            final String type = ColumnUtils.getType(rs.getString(2), precision, scale);
            final ColumnData cd = new ColumnData();
            cd.setColumnName(name);
            cd.setDataType(type);
            cd.setColumnType(rs.getString(2));
            cd.setColumnComment(comment);
            cd.setPrecision(precision);
            cd.setScale(scale);
            cd.setCharmaxLength(charmaxLength);
            cd.setNullable(nullable);
            cd.setColumnKey(columnKey);
            columnList.add(cd);
        }
        rs.close();
        ps.close();
        con.close();
        return columnList;
    }

    /**
     * 获取实体的所有属性
     * @author 溪云阁
     * @param tableName
     * @return
     * @throws SQLException String
     */
    public static String getEntityFeilds(String tableName) throws Exception {
        final List<ColumnData> dataList = getColumnDatas(tableName);
        final StringBuffer str = new StringBuffer();
        final StringBuffer getset = new StringBuffer();
        int i = 0;
        for (final ColumnData d : dataList) {
            i++;
            final String name = ColumnUtils.formatUnderlineNameFirstNameLower(d.getColumnName());
            final String type = d.getDataType();
            final String comment = d.getColumnComment();
            final String columnKey = d.getColumnKey();
            // 开始构建变量
            str.append("\r\t");
            str.append("// " + comment);
            str.append("\r\t");
            // 当为主键的时候
            if ("PRI".equals(columnKey.toUpperCase())) {
                str.append("@TableId(value = \"" + d.getColumnName() + "\", type = IdType.ASSIGN_ID)");
                str.append("\r\t");
            } else {
                str.append("@TableField(value=\"" + d.getColumnName() + "\")");
                str.append("\r\t");
            }
            str.append("private ");
            str.append(type + " ");
            str.append(name);
            str.append(";");
            if (i != dataList.size()) {
                str.append("\r\t");
            }
        }
        return str.toString() + getset.toString();
    }

    /**
     * 获取入参的所有属性
     * @author 溪云阁
     * @param tableName
     * @return
     * @throws SQLException String
     */
    public static String getInFeilds(String tableName) throws Exception {
        final List<ColumnData> dataList = getColumnDatas(tableName);
        final StringBuffer str = new StringBuffer();
        final StringBuffer getset = new StringBuffer();
        int i = 0;
        for (final ColumnData d : dataList) {
            if (!"PRI".equals(d.getColumnKey().toUpperCase())) {
                i++;
                final String name = ColumnUtils.formatUnderlineNameFirstNameLower(d.getColumnName());
                final String type = d.getDataType();
                final String comment = d.getColumnComment();
                final String nullAble = d.getNullable();
                // 开始构建变量
                if ("N".equals(nullAble)) {
                    str.append("\r\t");
                    str.append("@ApiModelProperty(value = \"" + comment + "\", required = true)");
                    str.append("\r\t");
                    str.append("@NotBlank(message = \"" + comment + "必填 \")");
                } else {
                    str.append("\r\t");
                    str.append("@ApiModelProperty(value = \"" + comment + "\")");
                }
                if ("String".equals(type) && StringUtils.isNotEmpty(d.getCharmaxLength())) {
                    str.append("\r\t");
                    str.append("@Length(max = " + d.getCharmaxLength() + ", message = \"" + comment + "最大长度为" + d.getCharmaxLength() + "\")");
                }
                str.append("\r\t");
                str.append("private ");
                str.append(type + " ");
                str.append(name);
                str.append(";");
                if (i != dataList.size()) {
                    str.append("\r\t");
                }
            }

        }
        return str.toString() + getset.toString();
    }

    /**
     * 获取出参的所有属性
     * @author 溪云阁
     * @param tableName
     * @return
     * @throws SQLException String
     */
    public static String getOutFeilds(String tableName) throws Exception {
        final List<ColumnData> dataList = getColumnDatas(tableName);
        final StringBuffer str = new StringBuffer();
        final StringBuffer getset = new StringBuffer();
        int i = 0;
        for (final ColumnData d : dataList) {
            i++;
            final String name = ColumnUtils.formatUnderlineNameFirstNameLower(d.getColumnName());
            final String type = d.getDataType();
            final String comment = d.getColumnComment();
            // 开始构建变量
            str.append("\r\t");
            str.append("@ApiModelProperty(value = \"" + comment + "\")");
            str.append("\r\t");
            str.append("private ");
            str.append(type + " ");
            str.append(name);
            str.append(";");
            if (i != dataList.size()) {
                str.append("\r\t");
            }
        }
        return str.toString() + getset.toString();
    }

    /**
     * 获取实体类的包
     * @author 溪云阁
     * @param tableName
     * @return
     * @throws SQLException String
     */
    public static String getEntityPacking(String tableName) throws Exception {
        final List<ColumnData> dataList = getColumnDatas(tableName);
        final StringBuilder importTypes = new StringBuilder();
        final Set<String> set = new HashSet<>();
        for (final ColumnData d : dataList) {
            set.addAll(initEntityPacking(d.getDataType()));
        }
        for (final String str : set) {
            importTypes.append(str);
        }
        return importTypes.toString();
    }

    /**
     * 初始化实体类的包
     * @author 溪云阁
     * @param dataType
     * @return Set<String>
     */
    private static Set<String> initEntityPacking(String dataType) {
        final Set<String> set = new HashSet<>();
        if (dataType.toLowerCase().contains("bigdecimal")) {
            set.add("import java.math.BigDecimal;\r\t");
        }
        if (dataType.toLowerCase().contains("date")) {
            set.add("import java.util.Date;\r\t");
        }
        if (dataType.toLowerCase().contains("timestamp")) {
            set.add("import java.sql.Timestamp;\r\t");
        }
        if (dataType.toLowerCase().contains("clob")) {
            set.add("import java.sql.Clob;\r\t");
        }
        return set;
    }

    /**
     * 获取入参的包
     * @author 溪云阁
     * @param tableName
     * @return
     * @throws SQLException String
     */
    public static String getInPacking(String tableName) throws Exception {
        final List<ColumnData> dataList = getColumnDatas(tableName);
        final StringBuilder importTypes = new StringBuilder();
        final Set<String> set = new HashSet<>();
        for (final ColumnData d : dataList) {
            set.addAll(initInPacking(d));
        }
        for (final String str : set) {
            importTypes.append(str);
        }
        return importTypes.toString();
    }

    /**
     * 初始化入参的包
     * @author 溪云阁
     * @param d
     * @return Set<String>
     */
    private static Set<String> initInPacking(ColumnData d) {
        final Set<String> set = new HashSet<>();
        final String dataType = d.getDataType();
        final String nullAble = d.getNullable();
        final String isPk = d.getColumnKey().toUpperCase();
        if (dataType.toLowerCase().contains("bigdecimal")) {
            set.add("import java.math.BigDecimal;\r\t");
        }
        if (dataType.toLowerCase().contains("date")) {
            set.add("import java.util.Date;\r\t");
        }
        if (dataType.toLowerCase().contains("timestamp")) {
            set.add("import java.sql.Timestamp;\r\t");
        }
        if (dataType.toLowerCase().contains("clob")) {
            set.add("import java.sql.Clob;\r\t");
        }
        if ("String".equals(dataType) && StringUtils.isNotEmpty(dataType)) {
            set.add("import org.hibernate.validator.constraints.Length;\r\t");
        }
        if ("N".equals(nullAble) && !"PRI".equals(isPk)) {
            set.add("import javax.validation.constraints.NotBlank;\r\t");
        }
        return set;
    }

    /**
     * 获取出参的包
     * @author 溪云阁
     * @param tableName
     * @return
     * @throws SQLException String
     */
    public static String getOutPacking(String tableName) throws Exception {
        final List<ColumnData> dataList = getColumnDatas(tableName);
        final StringBuilder importTypes = new StringBuilder();
        final Set<String> set = new HashSet<>();
        for (final ColumnData d : dataList) {
            set.addAll(initOutPacking(d.getDataType()));
        }
        for (final String str : set) {
            importTypes.append(str);
        }
        return importTypes.toString();
    }

    /**
     * 初始化出参的包
     * @author 溪云阁
     * @param dataType
     * @return Set<String>
     */
    private static Set<String> initOutPacking(String dataType) {
        final Set<String> set = new HashSet<>();
        if (dataType.toLowerCase().contains("bigdecimal")) {
            set.add("import java.math.BigDecimal;\r\t");
        }
        if (dataType.toLowerCase().contains("date")) {
            set.add("import java.util.Date;\r\t");
        }
        if (dataType.toLowerCase().contains("timestamp")) {
            set.add("import java.sql.Timestamp;\r\t");
        }
        if (dataType.toLowerCase().contains("clob")) {
            set.add("import java.sql.Clob;\r\t");
        }
        return set;
    }

}
